package com.collection.util;

import java.math.BigDecimal;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;

public class ExportPoiUtil {

    /**
     * 
     * 功能描述: 读取数据库并导出报表
     * 
     * @param list
     * @param headers
     * @param workbook
     * @param widths
     * @param title void
     * @version 1.0.0
     */
    public static <T> void exportXLS(List<LinkedHashMap<String, Object>> list,
                                     String[] headers,
                                     HSSFWorkbook workbook,
                                     int[] widths,
                                     String title) {

        // 2.创建一个 worksheet
        HSSFSheet worksheet = workbook.createSheet("sheet");

        // 3.定义起始行和列
        int startRowIndex = 0;
        int startColIndex = 0;

        // 4.创建title,data,headers
        buildReport(worksheet, startRowIndex, startColIndex, headers, widths, title);

        // 5.填充数据
        fillReport(worksheet, startRowIndex, startColIndex, list);

    }

    private static void buildReport(HSSFSheet worksheet,
                                    int startRowIndex,
                                    int startColIndex,
                                    String[] headers,
                                    int[] widths,
                                    String title) {

        for (int i = 0; i < widths.length; i++) {
            int width = widths[i];
            worksheet.setColumnWidth(i, width);
        }

        buildTitle(worksheet, startRowIndex, startColIndex, headers.length, title);
        buildHeaders(worksheet, startRowIndex, startColIndex, headers);
    }

    private static void buildHeaders(HSSFSheet worksheet,
                                     int startRowIndex,
                                     int startColIndex,
                                     String[] headers) {

        // Header字体
        Font font = worksheet.getWorkbook().createFont();
        font.setBoldweight((short) Font.BOLDWEIGHT_BOLD);

        // 单元格样式
        HSSFCellStyle headerCellStyle = worksheet.getWorkbook().createCellStyle();

        headerCellStyle.setAlignment(CellStyle.ALIGN_CENTER);
        headerCellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        headerCellStyle.setWrapText(true);
        headerCellStyle.setFont(font);
        headerCellStyle.setBorderBottom(CellStyle.BORDER_THIN);
        headerCellStyle.setBorderLeft(CellStyle.BORDER_THIN);
        headerCellStyle.setBorderRight(CellStyle.BORDER_THIN);
        headerCellStyle.setBorderTop(CellStyle.BORDER_THIN);

        // 产生表格标题行
        // 创建字段标题
        HSSFRow rowHeader = worksheet.createRow((short) startRowIndex + 1);
        rowHeader.setHeight((short) 500);

        for (int i = 0; i < headers.length; i++) {
            HSSFCell cell = rowHeader.createCell(i);
            cell.setCellStyle(headerCellStyle);
            HSSFRichTextString text = new HSSFRichTextString(headers[i]);
            cell.setCellValue(text);
        }
    }

    private static void buildTitle(HSSFSheet worksheet,
                                   int startRowIndex,
                                   int startColIndex,
                                   int rowlength,
                                   String title) {

        // 报表标题字体
        Font fontTitle = worksheet.getWorkbook().createFont();
        fontTitle.setBoldweight((short) Font.BOLDWEIGHT_BOLD);
        fontTitle.setFontHeight((short) 280);

        // 标题单元格格式
        HSSFCellStyle cellStyleTitle = worksheet.getWorkbook().createCellStyle();
        cellStyleTitle.setAlignment(CellStyle.ALIGN_CENTER);
        cellStyleTitle.setWrapText(true);
        cellStyleTitle.setFont(fontTitle);

        HSSFRow rowTitle = worksheet.createRow((short) startRowIndex);
        rowTitle.setHeight((short) 500);
        HSSFCell cellTitle = rowTitle.createCell(startColIndex);
        cellTitle.setCellValue(title);
        cellTitle.setCellStyle(cellStyleTitle);

        worksheet.addMergedRegion(new CellRangeAddress(0, 0, 0, rowlength - 1));// 标题合并列

    }

    /**
     * 数据填充 fillReport
     * 
     * @author robo
     * @date 2014-9-23 下午5:26:30
     * @param worksheet
     * @param startRowIndex
     * @param startColIndex
     * @param list
     */
    private static void fillReport(HSSFSheet worksheet,
                                   int startRowIndex,
                                   int startColIndex,
                                   List<LinkedHashMap<String, Object>> list) {

        startRowIndex += 2;
        HSSFCellStyle bodyCellStyle = worksheet.getWorkbook().createCellStyle();
        bodyCellStyle.setAlignment(CellStyle.ALIGN_CENTER);
        bodyCellStyle.setWrapText(false); // 是否自动换行.
        bodyCellStyle.setBorderBottom(CellStyle.BORDER_THIN);
        bodyCellStyle.setBorderLeft(CellStyle.BORDER_THIN);
        bodyCellStyle.setBorderRight(CellStyle.BORDER_THIN);
        bodyCellStyle.setBorderTop(CellStyle.BORDER_THIN);
        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        for (int i = 0; i < list.size(); i++) {
            LinkedHashMap<String, Object> map = list.get(i);
            Row row = worksheet.createRow(startRowIndex + i);
            int j = 0;
            for (Map.Entry<String, Object> entry : map.entrySet()) {
               
                Object cellValue = entry.getValue();
                Cell cell = row.createCell(j);

                cell.setCellStyle(bodyCellStyle);
                if (cellValue==null) {
                    cell.setCellValue("");
                } else {
                    if(cellValue.getClass() == BigDecimal.class){
                        BigDecimal tempVal = (BigDecimal)cellValue;
                        cell.setCellValue(tempVal.doubleValue());
                    }else if (cellValue.getClass() == Timestamp.class){
                        Timestamp tempVal = (Timestamp)cellValue;
                        cell.setCellValue(df.format(new Date(tempVal.getTime())));
                    }else if (cellValue.getClass() == Boolean.class){
                        Boolean tempVal = (Boolean)cellValue;
                        cell.setCellValue(tempVal);
                    }else{
                        cell.setCellValue(cellValue.toString());
                    }
                }
                j++;
            }

        }

    }

    /**
     * 
     * 功能描述: 从excel中读取数据 并返回 结果集合
     * 
     * @param workbook 
     * @param sheetNum 
     * @param startRow 起始行
     * @param columns 列表字段
     * @return List<Map<String, Object>>
     * @version 1.0.0
     * @author liuxl
     */
    public static List<Map<String, Object>> getListFromExcel(Workbook workbook,
                                                                 int sheetNum,int startRow,
                                                                 List<String> columns) {
        Sheet sheet = workbook.getSheetAt(sheetNum);

        // 解析公式结果
        FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();

        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        int minRowIx = sheet.getFirstRowNum();
        startRow = startRow<minRowIx ?minRowIx:startRow;
        int maxRowIx = sheet.getLastRowNum();
        for (int rowIx = startRow; rowIx <= maxRowIx; rowIx++) {
            Row row = sheet.getRow(rowIx);
            Map<String, Object> dtInfo = new HashMap<String, Object>();

            int minColIx = row.getFirstCellNum();
            int maxColIx = row.getLastCellNum();
            for (int colIx = minColIx; colIx <= maxColIx; colIx++) {
                Cell cell = row.getCell(colIx);
                CellValue cellValue = evaluator.evaluate(cell);
                if (cellValue == null) {
                    continue;
                }
                Object value = null;
                // 经过公式解析，最后只存在Boolean、Numeric和String三种数据类型，此外就是Error了
                // 其余数据类型，根据官方文档，完全可以忽略http://poi.apache.org/spreadsheet/eval.html
                switch (cellValue.getCellType()) {
                    case Cell.CELL_TYPE_BOOLEAN:
                        value = cellValue.getBooleanValue();
                        break;
                    case Cell.CELL_TYPE_NUMERIC:
                        // 这里的日期类型会被转换为数字类型，需要判别后区分处理
                        if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
                            value = cell.getDateCellValue();
                        } else {
                            value = cellValue.getNumberValue();
                        }
                        break;
                    case Cell.CELL_TYPE_STRING:
                        value = cellValue.getStringValue();
                        break;
                    case Cell.CELL_TYPE_FORMULA:
                        break;
                    case Cell.CELL_TYPE_BLANK:
                        break;
                    case Cell.CELL_TYPE_ERROR:
                        break;
                    default:
                        break;
                }
                dtInfo.put(columns.get(colIx), value);
            }
            list.add(dtInfo);
        }
        return list;
    }
    
    
    public static String toUtf8String(String s){ 
        StringBuffer sb = new StringBuffer(); 
          for (int i=0;i<s.length();i++){ 
             char c = s.charAt(i); 
             if (c >= 0 && c <= 255){sb.append(c);} 
           else{ 
           byte[] b; 
            try { b = Character.toString(c).getBytes("utf-8");} 
            catch (Exception ex) { 
                System.out.println(ex); 
                     b = new byte[0]; 
            } 
               for (int j = 0; j < b.length; j++) { 
                int k = b[j]; 
                 if (k < 0) k += 256; 
                 sb.append("%" + Integer.toHexString(k).toUpperCase()); 
                 } 
        } 
     } 
     return sb.toString(); 
   }

}
